Using temporary与Using filesort

mysql

通过explain查看sql的执行计划时，Extra字段的值往往会看到Using where; Using index; Using temporary; Using filesort，其中此次重点关注Using temporary; Using filesort。

Using temporary

Using temporary表示由于排序没有走索引、使用union、子查询连接查询、使用某些视图等原因（详见internal-temporary-tables），因此创建了一个内部临时表。注意这里的临时表可能是内存上的临时表，也有可能是硬盘上的临时表，理所当然基于内存的临时表的时间消耗肯定要比基于硬盘的临时表的实际消耗小。

查看sql执行时使用的是内存临时表还是硬盘临时表，需要使用如下命令：

1
2
3
4
5
6
7
8
9
mysql> show global status like '%tmp%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0     |
| Created_tmp_files       | 5     |
| Created_tmp_tables      | 11    |
+-------------------------+-------+
3 rows in set

Created_tmp_tables表示mysql创建的内部临时表的总数（包括内存临时表和硬盘临时表）；Created_tmp_disk_tables表示mysql创建的硬盘临时表的总数。

当mysql需要创建临时表时，选择内存临时表还是硬盘临时表取决于参数tmp_table_size和max_heap_table_size，内存临时表的最大容量为tmp_table_size和max_heap_table_size值的最小值，当所需临时表的容量大于两者的最小值时，mysql就会使用硬盘临时表存放数据。

用户可以在mysql的配置文件里修改该两个参数的值，两者的默认值均为16M。

1
2
tmp_table_size = 16M
max_heap_table_size = 16M

查看tmp_table_size和max_heap_table_size值：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> show global variables like 'max_heap_table_size';
+---------------------+----------+
| Variable_name       | Value    |
+---------------------+----------+
| max_heap_table_size | 16777216 |
+---------------------+----------+
1 row in set

mysql> show global variables like 'tmp_table_size';
+----------------+----------+
| Variable_name  | Value    |
+----------------+----------+
| tmp_table_size | 16777216 |
+----------------+----------+
1 row in set

Using filesort

如果问Using filesort是什么意思，大多数人应该会回答“基于硬盘的排序”或者“数据太多不适合内存，所以在硬盘上排序”。然而这些解释是错误的。

Using filesort仅仅表示没有使用索引的排序，事实上filesort这个名字很糟糕，并不意味着在硬盘上排序，filesort与文件无关。因此消除Using filesort的方法就是让查询sql的排序走索引。

filesort使用的算法是QuickSort，即对需要排序的记录生成元数据进行分块排序，然后再使用mergesort方法合并块。其中filesort可以使用的内存空间大小为参数sort_buffer_size的值，默认为2M。当排序记录太多sort_buffer_size不够用时，mysql会使用临时文件来存放各个分块，然后各个分块排序后再多次合并分块最终全局完成排序。

1
2
3
4
5
6
7
mysql> show global variables like 'sort_buffer_size';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| sort_buffer_size | 262144 |
+------------------+--------+
1 row in set

Sort_merge_passes表示filesort执行过的文件分块合并次数的总和，如果该值比较大，建议增大sort_buffer_size的值。

1
2
3
4
5
6
7
8
9
10
mysql> show global status like '%sort%';
+-------------------+---------+
| Variable_name     | Value   |
+-------------------+---------+
| Sort_merge_passes | 226     |
| Sort_range        | 0       |
| Sort_rows         | 1384911 |
| Sort_scan         | 6       |
+-------------------+---------+
4 rows in set

filesort排序方式

filesort使用的排序方法有两种：

第一种方法是对需要排序的记录生成<sort_key,rowid>的元数据进行排序，该元数据仅包含排序字段和rowid。排序完成后只有按字段排序的rowid，因此还需要通过rowid进行回表操作获取所需要的列的值，可能会导致大量的随机IO读消耗；

第二种方法是是对需要排序的记录生成<sort_key, additional_fields>的元数据，该元数据包含排序字段和需要返回的所有列。排序完后不需要回表，但是元数据要比第一种方法长得多，需要更多的空间用于排序。

参数max_length_for_sort_data字段用于控制filesort使用的排序方法，当所有需要排序记录的字段数量总和小于max_length_for_sort_data时使用第二种算法，否则会用第一种算法。该值的默认值为1024。

1
2
3
4
5
6
7
mysql> show global variables like 'max_length_for_sort_data';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| max_length_for_sort_data | 1024  |
+--------------------------+-------+
1 row in set
1
mysql> set global max_length_for_sort_data = 1024;
>